![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
So far, weve been concentrating on how to use the classes in the JDBC to perform SQL queries. Thats great, but now we have to do something with the data weve retrieved. The end user of your JDBC applets or applications will want to see more than just rows and rows of data. In this chapter, well learn how to package the raw table data that is returned after a successful SQL query into a Java object, and then how to use this packaged data to produce easy-to-read graphs.
The first issue well look at is creating a Java object to store the results of a query. This object will provide a usable interface to the actual query results so they can be plugged into a Java graphics library. Well create a simple data structure to hold the column results in a formatted way so that we can easily parse them and prepare them for display. Second, well look at taking these results in the Java object and setting up the necessary code to plug the data into a pie chart and bar graph Java package.
In the next chapter, well go one step further and work with BLOB data types (like images). Between these chapters, I will be providing plenty of examples, complete with code, to help you work up your own JDBC programs. At the very least, these chapters will give you some ideas for dealing with raw table data and displaying it in an effective manner.
Although the JDBC provides you with the ResultSet class to get the data from an SQL query, you will still need to store and format within your program the results for display. The smart way to do this is in a re-usable fashion (by implementing a generic object or class) which allows you to re-use the same class you develop to retrieve data from a query in any of your JDBC programs. The code snippet in Listing 7.1 is a method that will keep your results in a Java object until you are ready to parse and display it.
Lets begin by defining the data we will be getting from the source, and determining how we want to structure it within our Java applet. Remember that the ResultSet allows us to retrieve data in a row-by-row, column-by-column fashion; it simply gives us sequential access to the resulting table data. Table 7.1 shows the example table we will be using in this chapter.
emp_no | first_name | last_name | salary |
---|---|---|---|
01234 | Pratik | Patel | 8000 |
1235 | Karl | Moss | 23000 |
0002 | Keith | Weiskamp | 90000 |
0045 | Ron | Pronk | 59999 |
0067 | David | Friedel | 53000 |
The optimal way to store this data in our Java program is to put each columns data in its own structure and then link the different columns by using an index; this will allow us to keep the columnar relationship of the table intact. We will put each columns data in an array. To simplify matters, well use the getString method, which translates the different data types returned by a query into a String type. Then, well take the data in a column and delimit the instances with commas. Well use an array of String to do this; each place in the array will represent a different column. The data object we will create is shown here:
table_data[0] => 01234,1235,0002,0045,0067 table_data[1] => Pratik,Karl,Keith,Ron,David table_data[2] => Patel,Moss,Weiskamp,Pronk,Friedel table_data[3] => 8000,23000,90000,59999,53000
Listing 7.1 shows the method well use to query the database and return a String array that contains the resulting table data.
Listing 7.1 The getData method.
public String[] getData( String QueryLine ) { // Run the QueryLine SQL query, and return the resulting columns in an // array of String. The first column is at index [0], the second at [1], // etc. int columns, pos; String column[]=new String[4]; // We have to initialize the column String variable even though we re- // declare it below. The reason is because the declaration below is in a // try{} statement, and the compiler will complain that the variable may // not be initialized. boolean more; try { Statement stmt = con.createStatement(); // Create a Statement object from the // Connection.createStatement method. ResultSet rs = stmt.executeQuery(QueryLine); // Execute the passed in query, and get // the ResultSet for the query. columns=(rs.getMetaData()).getColumnCount(); // Get the number of columns in the resulting table so we can // declare the column String array, and so we can loop // through the results and retrieve them. column = new String[columns]; // Create the column variable to be the exact number of // columns that are in the result table. // Initialize the column array to be blank since we'll be adding // directly to them later. for(pos=1; pos<=columns; pos++) { column[pos-1]=""; } more=rs.next(); // Get the first row of the ResultSet. Loop through the ResultSet // and get the data, row-by-row, column-by-column. while(more) { for (pos=1; pos<=columns; pos++) { column[pos-1]+=(rs.getString(pos)); // Add each column to the respective column[] String array. } more=rs.next(); // Get the next row of the result if it exists. // Now add a comma to each array element to delimit this row is // done. for (pos=1; pos<=columns; pos++) { if(more) { // We only want to do this if this isn't the last row of the // table! column[pos-1]+=(","); } } } stmt.close(); // All done. Close the statement object. } catch( Exception e ) { e.printStackTrace(); System.out.println(e.getMessage()); } return column; // Finally, return the entire column[] array. }
Now that we have the data nicely packaged into our Java object, how do we show it? The code in Listing 7.2 dumps the data in the object to the screen. We simply loop through the array and print the data.
Listing 7.2 Code to print retrieved data to the console.
public void ShowFormattedData(String[] columnD ) { int i; for ( i=0; i< columnD.length; i++) { System.out.println(columnD[i]+"\n"); } }
Previous | Table of Contents | Next |